package com.bolt.support.codegenerator.db;

import com.bolt.common.db.DbHelper;
import com.bolt.common.utils.StrUtil;
import com.bolt.convention.data.DefaultPageInfo;
import com.bolt.convention.data.PageInfo;
import com.bolt.convention.exception.ServiceException;
import com.bolt.support.codegenerator.dto.TableInfo;
import com.bolt.support.codegenerator.model.Table;
import com.bolt.support.sqlconfig.dialect.PagingPostgreSqlDialect;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2021/4/22.
 */
public class PostgreSqlTableFactory extends AbstractTableFactory {

    private static final String TABLE_COMMENTS_SQL = "select cast(obj_description(relfilenode,'pg_class') as varchar) as comment from pg_class where relname  = ?";

    public PostgreSqlTableFactory(String url, String username, String password, String driverClass) {
        super(url, username, password, driverClass);
    }

    @Override
    public Table getTable(String tableName) throws SQLException {
        Table table = super.getTable(tableName);
        if (table != null) {
            introspectTableComments(table, TABLE_COMMENTS_SQL, "COMMENT");
        }
        return table;
    }

    @Override
    public PageInfo<TableInfo> getTables(String tableName, int page, int size) throws SQLException {
        String sql = "SELECT A.tablename AS table_name, obj_description(relfilenode, 'pg_class') AS table_comment FROM pg_tables A, pg_class B WHERE A.schemaname='public' AND A.tablename = B.relname and A.tablename like ?";

        String totalSql = "select COUNT(*) FROM pg_tables A, pg_class B WHERE A.schemaname='public' AND A.tablename = B.relname and A.tablename like ?";

        PagingPostgreSqlDialect dialect = new PagingPostgreSqlDialect();

        String pageSql = dialect.getLimitString(sql, page * size, size);
        List<TableInfo> datas = new ArrayList<>();
        PreparedStatement pstmt = null;
        PreparedStatement countStmt = null;
        ResultSet rs = null;
        long total = 0;
        try {
            pstmt = getConnection().prepareStatement(pageSql);
            pstmt.setString(1, StrUtil.isNotBlank(tableName) ? ("%" + tableName + "%") : "%%");

             rs = pstmt.executeQuery();//执行查询操作
            while (rs.next()) {
                TableInfo tableInfo = new TableInfo();
                tableInfo.setTableName(rs.getString("table_name"));
                tableInfo.setRemark(rs.getString("table_comment"));
                datas.add(tableInfo);
            }
            countStmt = getConnection().prepareStatement(totalSql);
            countStmt.setString(1, StrUtil.isNotBlank(tableName) ? ("%" + tableName + "%") : "%%");
            ResultSet count = countStmt.executeQuery();
            if (count.next()) {
                total = count.getLong(1);
            }

        } catch (Exception e) {
            throw new ServiceException(e);
        } finally {
            DbHelper.close(pstmt);
            DbHelper.close(countStmt);
            DbHelper.close(connection);
        }
        return new DefaultPageInfo(page, size, datas, total);

    }
}

 